/*
	EXEC SP_LOAD_SALES_ORDERS_FOR_SHIPPING_LOCATIONS 0, 0, NULL, 500, 1, 0

*/
ALTER PROC SP_LOAD_SALES_ORDERS_FOR_SHIPPING_LOCATIONS ( @PRODUCT_ID INT,
						@CUSTOMER_ID INT,
						@SALESORDER_NO VARCHAR(50),
						@SCHEDULEDIN_DAYS INT,
						@SHOW_ONLY_NO_SHIP_LOCATION_SOs INT,
						@IS_SAMPLE INT )
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SALESORDER_ID INT
	DECLARE @LINEITEM_ID INT
	DECLARE @CUSTOMER_PO_NO VARCHAR(50)
	DECLARE @CUSTOMER_NAME VARCHAR(65)
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @REMAINING_QTY INT
	DECLARE	@COOL_REQUESTED_DATE DATETIME
	DECLARE @COOL_SCHEDULE_DATE DATETIME
	DECLARE @SHIPPING_LOCATION_IDS VARCHAR(1000)

	/*CREATE TABLE #RESULTS_TABLE
	(
		SALESORDER_ID INT, 
		SALESORDER_NO VARCHAR(50), 
		CUSTOMER_PO_NO VARCHAR(50),
		CUSTOMER_NAME VARCHAR(50),
		PART_NUMBER_QTY VARCHAR(4000), 
		COOL_REQUESTED_DATE DATETIME, 
		COOL_SCHEDULE_DATE DATETIME,
		SHIPPING_LOCATION_IDS VARCHAR(1000)
	)*/

	CREATE TABLE #LINE_ITEM_DETAILS
	( 
		LINEITEM_ID INT,
		LINEITEM_NO INT,
		SALESORDER_ID INT, 
		SALESORDER_NO VARCHAR(50), 
		CUSTOMER_PO_NO VARCHAR(50),
		CUSTOMER_ID INT,
		CUSTOMER_NAME VARCHAR(65),
		PROCESSSTEP_ID INT, 
		PART_NUMBER VARCHAR(50), 
		REMAINING_QTY INT,
		COOL_REQUESTED_DATE DATETIME, 
		COOL_SCHEDULE_DATE DATETIME,
		SHIPPING_LOCATION_IDS VARCHAR(1000)
	)
	
	DECLARE @LINE_ITEM_DETAILS_STR NVARCHAR(4000)

	SET @LINE_ITEM_DETAILS_STR = N'
	  INSERT INTO #LINE_ITEM_DETAILS 
		SELECT COOL.LINEITEM_ID,
			COOL.COOL_LINE_ITEM_NO, 
			SO.SALESORDER_ID, 
			SO.SALESORDER_NO,
			SO.SO_CUSTOMER_PO_NUMBER, 
			C.CUSTOMER_ID, 
			C.C_NAME, 
			P.PROCESSSTEP_ID, 
			P.PART_NUMBER, 
			( COOL.COOL_QTY - ISNULL( XYZ.TOTAL, 0 ) ) AS REMAINING_QTY, 		
			COOL.COOL_REQUESTED_DATE, 
			ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) AS COOL_SCHEDULE_DATE, 
			NULL
		FROM
		(
			(
				(
					CUSTOMER_OPEN_ORDER_LINEITEM COOL 
					LEFT OUTER JOIN 
					(
						SELECT LINEITEM_ID, SUM( CSLIT_QTY_SHIPPED ) AS TOTAL, MAX(CST.CST_CONFIRMED_ON) AS SHIP_DATE
						FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT WHERE CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT_STATUS = ''SHIPPED'' GROUP BY LINEITEM_ID
					) 
					XYZ ON COOL.LINEITEM_ID = XYZ.LINEITEM_ID
					INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID -- AND SO.SO_IS_SAMPLE = 1
				)
				INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
			)
			INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
		)
		WHERE ( COOL.COOL_QTY - ISNULL( XYZ.TOTAL, 0 ) ) > 0 '
	IF ( @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID > 0 )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND P.PRODUCT_ID = ''' + CONVERT(VARCHAR,@PRODUCT_ID) + ''''
	 END
	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID > 0 )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND SO.CUSTOMER_ID = ''' + CONVERT(VARCHAR,@CUSTOMER_ID) + ''''
	 END
	IF ( @SALESORDER_NO IS NOT NULL AND LEN(LTRIM(RTRIM(@SALESORDER_NO))) > 0 )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND SO.SALESORDER_NO = ''' + LTRIM(RTRIM(@SALESORDER_NO)) + ''''
	 END
	IF ( @SCHEDULEDIN_DAYS IS NOT NULL AND @SCHEDULEDIN_DAYS > 0 )
	BEGIN		
		SET @LINE_ITEM_DETAILS_STR =  @LINE_ITEM_DETAILS_STR + ' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < CONVERT( DATETIME,''' + CONVERT (VARCHAR, DATEADD(DD,@SCHEDULEDIN_DAYS,CURRENT_TIMESTAMP), 101)+' 11:59PM'' )' 
	END
	IF ( @IS_SAMPLE IS NOT NULL AND @IS_SAMPLE >= 0 )
	BEGIN		
		SET @LINE_ITEM_DETAILS_STR =  @LINE_ITEM_DETAILS_STR + ' AND SO.SO_IS_SAMPLE = ''' + CONVERT(VARCHAR,@IS_SAMPLE) + ''''
	END

	EXEC (@LINE_ITEM_DETAILS_STR)
	
	/*DECLARE @LINE_ITEM_CURSOR CURSOR

	SET @LINE_ITEM_CURSOR = CURSOR FOR SELECT SALESORDER_ID, SALESORDER_NO, SO_CUSTOMER_PO_NUMBER, CUSTOMER_NAME, PART_NUMBER, REMAINING_QTY, COOL_REQUESTED_DATE, COOL_SCHEDULE_DATE FROM #LINE_ITEM_DETAILS
	OPEN @LINE_ITEM_CURSOR
	FETCH NEXT FROM @LINE_ITEM_CURSOR INTO @SALESORDER_ID, @SALESORDER_NO, @CUSTOMER_PO_NO, @CUSTOMER_NAME, @PART_NUMBER, @REMAINING_QTY, @COOL_REQUESTED_DATE, @COOL_SCHEDULE_DATE
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		IF ( (SELECT COUNT(*) FROM #RESULTS_TABLE WHERE SALESORDER_ID = @SALESORDER_ID) = 0 ) --INSERT
		 BEGIN
			INSERT INTO #RESULTS_TABLE(SALESORDER_ID, SALESORDER_NO, CUSTOMER_PO_NO, CUSTOMER_NAME, PART_NUMBER_QTY, COOL_REQUESTED_DATE, COOL_SCHEDULE_DATE )
					 VALUES ( @SALESORDER_ID, @SALESORDER_NO, @CUSTOMER_PO_NO, @CUSTOMER_NAME, @PART_NUMBER+'/'+CONVERT(VARCHAR,@REMAINING_QTY), @COOL_REQUESTED_DATE, @COOL_SCHEDULE_DATE)
		 END
		ELSE --UPDATE
		 BEGIN
			DECLARE @SCH_DATE DATETIME
			SELECT @SCH_DATE = COOL_SCHEDULE_DATE FROM #RESULTS_TABLE WHERE SALESORDER_ID = @SALESORDER_ID 

			UPDATE #RESULTS_TABLE SET PART_NUMBER_QTY = PART_NUMBER_QTY + '#' + @PART_NUMBER+'/'+CONVERT(VARCHAR,@REMAINING_QTY) WHERE SALESORDER_ID = @SALESORDER_ID
			IF ( @COOL_SCHEDULE_DATE > @SCH_DATE )
			 BEGIN
				UPDATE #RESULTS_TABLE SET COOL_SCHEDULE_DATE = @COOL_SCHEDULE_DATE, COOL_REQUESTED_DATE = @COOL_REQUESTED_DATE WHERE SALESORDER_ID = @SALESORDER_ID
			 END
		 END
		
		SET @SALESORDER_ID = 0
		SET @SALESORDER_NO = NULL
		SET @CUSTOMER_PO_NO = NULL
		SET @CUSTOMER_NAME = NULL
		SET @PART_NUMBER = NULL
		SET @REMAINING_QTY = 0
		SET @COOL_REQUESTED_DATE = NULL
		SET @COOL_SCHEDULE_DATE = NULL
		FETCH NEXT FROM @LINE_ITEM_CURSOR INTO @SALESORDER_ID, @SALESORDER_NO, @CUSTOMER_PO_NO, @CUSTOMER_NAME, @PART_NUMBER, @REMAINING_QTY, @COOL_REQUESTED_DATE, @COOL_SCHEDULE_DATE
	 END
	CLOSE @LINE_ITEM_CURSOR
	DEALLOCATE @LINE_ITEM_CURSOR
	*/
	IF ( @SHOW_ONLY_NO_SHIP_LOCATION_SOs = 0 )
	 BEGIN
		--DELETE FROM #RESULTS_TABLE WHERE SALESORDER_ID IN ( SELECT DISTINCT SALESORDER_ID FROM SALES_ORDER_SHIPPING_LOCATIONS_TABLE )
		DELETE FROM #LINE_ITEM_DETAILS WHERE LINEITEM_ID IN ( SELECT DISTINCT LINEITEM_ID FROM SALES_ORDER_SHIPPING_LOCATIONS_TABLE )
	 END
	ELSE
	 BEGIN
		DECLARE @SO_CURSOR CURSOR
		DECLARE @SHIP_LOCATION_CURSOR CURSOR
		DECLARE @SHIP_LOCATION_ID INT
		SET @SO_CURSOR = CURSOR FOR SELECT LINEITEM_ID FROM #LINE_ITEM_DETAILS
		OPEN @SO_CURSOR
		FETCH NEXT FROM @SO_CURSOR INTO @LINEITEM_ID
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			SET @SHIP_LOCATION_CURSOR = CURSOR FOR SELECT SHIPPING_LOCATION_ID FROM SALES_ORDER_SHIPPING_LOCATIONS_TABLE WHERE LINEITEM_ID = @LINEITEM_ID
			OPEN @SHIP_LOCATION_CURSOR
			FETCH NEXT FROM @SHIP_LOCATION_CURSOR INTO @SHIP_LOCATION_ID
			WHILE @@FETCH_STATUS = 0
			 BEGIN
				UPDATE #LINE_ITEM_DETAILS SET SHIPPING_LOCATION_IDS = ISNULL(SHIPPING_LOCATION_IDS,'') + ',' + CONVERT(VARCHAR,@SHIP_LOCATION_ID) WHERE LINEITEM_ID = @LINEITEM_ID
				SET @SHIP_LOCATION_ID = 0
				FETCH NEXT FROM @SHIP_LOCATION_CURSOR INTO @SHIP_LOCATION_ID
			 END
			CLOSE @SHIP_LOCATION_CURSOR
			DEALLOCATE @SHIP_LOCATION_CURSOR
	
			SET @LINEITEM_ID = 0
			FETCH NEXT FROM @SO_CURSOR INTO @LINEITEM_ID
		 END
		CLOSE @SO_CURSOR
		DEALLOCATE @SO_CURSOR
	 END

	SELECT SALESORDER_ID, SALESORDER_NO, LINEITEM_ID, LINEITEM_NO, CUSTOMER_PO_NO, CUSTOMER_NAME, (PART_NUMBER+'/'+CONVERT(VARCHAR,REMAINING_QTY)) AS PART_NUMBER_QTY, COOL_REQUESTED_DATE, COOL_SCHEDULE_DATE, SHIPPING_LOCATION_IDS FROM #LINE_ITEM_DETAILS ORDER BY COOL_SCHEDULE_DATE ASC
END